/****** Object:  View [dbo].[vw_container]    Script Date: 03/31/2021 16:29:49 ******/

ALTER VIEW [dbo].[vw_container]
AS
SELECT     dbo.Invoice_GDN.Inv_GDNID, dbo.Invoice_GDN.Inv_GDNDate, dbo.Invoice_GDN.Inv_GDNVNo, dbo.Invoice_GDN.BLNo, dbo.Invoice_GDNitem.Dis_Date, 
                      dbo.Invoice_GDNitem.PartyID, dbo.Accounts.AccountTitle AS PartyName, dbo.Invoice_GDNitem.ItemID, dbo.vw_Items.ItemName, dbo.Invoice_GDNitem.VarietyID, 
                      dbo.vw_Items.VarietyName, dbo.Invoice_GDNitem.Qty, dbo.Invoice_GDNitem.ContainerNo, dbo.Invoice_GDNitem.DRemarks
FROM         dbo.Invoice_GDN INNER JOIN
                      dbo.Invoice_GDNitem ON dbo.Invoice_GDN.Inv_GDNID = dbo.Invoice_GDNitem.Inv_GDNID LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.Invoice_GDNitem.VarietyID = dbo.vw_Items.VarietyID LEFT OUTER JOIN
                      dbo.Accounts ON dbo.Invoice_GDNitem.PartyID = dbo.Accounts.AccountNo

----------------------------------------------------------------------------------------------

/****** Object:  View [dbo].[vw_BLNo_Bank_GDN]    Script Date: 03/31/2021 16:30:17 ******/

CREATE VIEW [dbo].[vw_BLNo_Bank_GDN]
AS
SELECT     dbo.Invoice_Bank.Inv_BankID, dbo.Invoice_Bank.Inv_BankDate, dbo.Invoice_Bank.Inv_BankVNo AS V_No, dbo.Invoice_Bank.BLNo AS BL_No, 
                      dbo.Invoice_Bank.BLDate AS BL_Date, dbo.Invoice_Bank.ConsigneeID AS Party_ID, dbo.Accounts.AccountTitle AS Party_Name, 
                      dbo.Invoice_Bank.BLNo + ' ' + dbo.Invoice_BankItem.ContainerNo + ' ' + dbo.Accounts.AccountTitle AS Search, dbo.Invoice_BankItem.ItemID, dbo.Items.ItemName, 
                      dbo.Invoice_BankItem.VarietyID, dbo.Varietys.VarietyName, dbo.Invoice_BankItem.LQTY, dbo.Invoice_BankItem.ContainerNo AS Container_No, 
                      dbo.Invoice_Bank.Remarks
FROM         dbo.Invoice_Bank INNER JOIN
                      dbo.Accounts ON dbo.Invoice_Bank.ConsigneeID = dbo.Accounts.AccountNo INNER JOIN
                      dbo.Invoice_BankItem ON dbo.Invoice_Bank.Inv_BankID = dbo.Invoice_BankItem.inv_BankID LEFT OUTER JOIN
                      dbo.Varietys ON dbo.Invoice_BankItem.VarietyID = dbo.Varietys.VarietyID LEFT OUTER JOIN
                      dbo.Items ON dbo.Invoice_BankItem.ItemID = dbo.Items.ItemID
WHERE     (dbo.Invoice_Bank.Inv_BankVNo LIKE 'BI-%') AND (dbo.Invoice_Bank.BLNo NOT IN
                          (SELECT DISTINCT BLNo
                            FROM          dbo.Invoice_GDN))
UNION ALL
SELECT     dbo.Invoice_GRN.Inv_GRNID, dbo.Invoice_GRN.Inv_GRNDate, dbo.Invoice_GRN.Inv_GRNVNo, dbo.Invoice_GRN.BENo, dbo.Invoice_GRN.BEDate, 
                      dbo.Invoice_GRN.SupplierID AS PartyID, Accounts_1.AccountTitle AS PartyName, 
                      dbo.Invoice_GRN.BENo + ' ' + dbo.Invoice_GRNItem.ContainerNo + ' ' + Accounts_1.AccountTitle AS Search, dbo.Invoice_GRNItem.ItemID, dbo.vw_Items.ItemName, 
                      dbo.Invoice_GRNItem.VarietyID, dbo.vw_Items.VarietyName, dbo.Invoice_GRNItem.LQTY, dbo.Invoice_GRNItem.ContainerNo, dbo.Invoice_GRN.Remarks
FROM         dbo.Invoice_GRNItem INNER JOIN
                      dbo.Invoice_GRN ON dbo.Invoice_GRNItem.inv_GRNID = dbo.Invoice_GRN.Inv_GRNID LEFT OUTER JOIN
                      dbo.Accounts AS Accounts_1 ON dbo.Invoice_GRN.SupplierID = Accounts_1.AccountNo LEFT OUTER JOIN
                      dbo.vw_Items ON dbo.Invoice_GRNItem.VarietyID = dbo.vw_Items.VarietyID
WHERE     (dbo.Invoice_GRN.Inv_GRNVNo LIKE 'GRN-%') AND (dbo.Invoice_GRN.BENo NOT IN
                          (SELECT DISTINCT BLNo
                            FROM          dbo.Invoice_GDN AS Invoice_GDN_1))